﻿using _9jCardRegisterCollection.Helper;
using _9jCardRegisterCollection.Models;
using AntdUI;
using CardRegisterCollection.Utility;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.OpenXml4Net.OPC.Internal;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.Streaming.Values;
using Org.BouncyCastle.Utilities;
using SixLabors.Fonts;
using SixLabors.ImageSharp.PixelFormats;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Windows.Input;
using ZXing;
using ZXing.QrCode;
using ZXing.QrCode.Internal;

namespace _9jCardRegisterCollection.Views
{
    public partial class CarExportExcel : Form
    {
        public CarExportExcel(AntdUI.Window Window, AntList<CarExpand> cars)
        {
            car = cars;
            window = Window;
            InitializeComponent();

            dt = new DataTable();
            dt.Columns.Add("序号", System.Type.GetType("System.String"));
            dt.Columns.Add("车牌号码", System.Type.GetType("System.String"));
            dt.Columns.Add("驾驶员姓名", System.Type.GetType("System.String"));
            dt.Columns.Add("身份证件号码", System.Type.GetType("System.String"));
            dt.Columns.Add("电话号码", System.Type.GetType("System.String"));
            dt.Columns.Add("使用单位", System.Type.GetType("System.String"));
            dt.Columns.Add("申请证件类型", System.Type.GetType("System.String"));
            dt.Columns.Add("备注一", System.Type.GetType("System.String"));

            for (int i = 0; i < car.Count; i++)
            {
                DataRow dr = dt.NewRow();   
                dr["序号"] = i.ToString();
                dr["车牌号码"] = car[i].CarNumber.ToString();
                dr["驾驶员姓名"] = car[i].DriverName.ToString();
                dr["身份证件号码"] = car[i].DriverIDNumber.ToString();
                dr["电话号码"] = car[i].Telephone.ToString();
                dr["使用单位"] = car[i].UseUnit.ToString();
                dr["申请证件类型"] = car[i].CardType.ToString();
                dt.Rows.Add(dr);
            }
        }
        static DataTable dt;
        AntdUI.Window window;
        AntList<CarExpand> car;
        string USER_PIC_FOLDER_PATH = string.Empty;
        List<string> list = new List<string>();
        /// <summary>
        /// 验证失败的人员
        /// </summary>
        private int CheckErrorPersonCount = 0;
        private int CheckWarnPersonCount = 0;

        private void btnExport_Click(object sender, EventArgs e)
        {
            
            //FolderBrowserDialog dialog = new FolderBrowserDialog();
            //dialog.Description = "请选择数据头像文件夹路径\n1.单张照片小于1.5M  2.命名格式：姓名证件号码.jpg\n3.清晰度需 照片宽度*水平分辨率>24000";

            //if (dialog.ShowDialog() == DialogResult.OK)
            //{
            this.button1.Enabled = true;
            //    USER_PIC_FOLDER_PATH = dialog.SelectedPath;
            //    this.label1.Text = string.Format("您已经【{0}】作为人员数据头像匹配的路径", USER_PIC_FOLDER_PATH);
            //}

            InitView();

        }

        private void InitView()
        {
            DataTable dtView = dt.Copy();
           
            dtView.Columns.Add("图片", typeof(byte[]));
            dtView.Columns.Add("提示", System.Type.GetType("System.String"));
            lvwUsers.Items.Clear();
            this.lvwUsers.BeginUpdate();
            this.lvwUsers.View = View.Details;
            this.lvwUsers.SmallImageList = this.imgUserPic;
            this.lvwUsers.Columns.Add("照片", 100, System.Windows.Forms.HorizontalAlignment.Center);
            this.lvwUsers.Columns.Add("人员姓名", 100, System.Windows.Forms.HorizontalAlignment.Left);
            this.lvwUsers.Columns.Add("身份证件号码", 160, System.Windows.Forms.HorizontalAlignment.Left);
            this.lvwUsers.Columns.Add("对应照片", 180, System.Windows.Forms.HorizontalAlignment.Left);
            this.lvwUsers.Columns.Add("消息", 200, System.Windows.Forms.HorizontalAlignment.Left);

            for (int i = 0; i < dtView.Rows.Count; i++)
            {
                var row = dtView.Rows[i];
                ListViewItem listViewItem = new ListViewItem();
                listViewItem.ImageIndex = i;
                listViewItem.SubItems.Add(row["人员姓名"].ToString());
                listViewItem.SubItems.Add(row["身份证件号码"].ToString());
                listViewItem.Tag = i;

                var imageName = row["人员姓名"].ToString() + row["身份证件号码"].ToString();

                    //        msg += "头像文件损坏，请更换;";
                    //        listViewItem.ForeColor = Color.Red;
                    //        this.imgUserPic.Images.Add(imageList1.Images[0]);
                    //    }
                    //    else
                    //    {
                    //        CheckWarnPersonCount++;
                    //        this.imgUserPic.Images.Add(img);
                    //    }
                    //    listViewItem.SubItems.Add(msg);
                // 无法匹配照片
                if (listViewItem.ForeColor != Color.Red)
                {
                    // 照片匹配成功，检测照片合法性
                    //var file = files.FirstOrDefault(_ => Path.GetFileName(_.FullName) == orgImageName);
                    //if (files != null)
                    //{
                    //    }
                    //}
                }
                this.lvwUsers.Items.Add(listViewItem);
            }
            this.lvwUsers.EndUpdate();
        }

        private string SelectedFolderPath()
        {
            FolderBrowserDialog dialog = new FolderBrowserDialog();
            dialog.Description = "请选择导出文件路径";

            if (dialog.ShowDialog() == DialogResult.OK)
            {
                return dialog.SelectedPath;
            }
            return string.Empty;
        }
        public void DeleteFolder(string dir)
        {
            if (Directory.Exists(dir)) //如果存在这个文件夹删除之
            {
                foreach (string d in Directory.GetFileSystemEntries(dir))
                {
                    if (File.Exists(d))
                        File.Delete(d); //直接删除其中的文件
                    else
                        DeleteFolder(d); //递归删除子文件夹
                }
                Directory.Delete(dir, true); //删除已空文件夹
            }
        }


        private void Export1(string strSelectedPath, DataTable dt2)
        {
            ApplicationFormParam param = new ApplicationFormParam()
            {
                Title = "车辆证件申领表",
                FileFullPath = strSelectedPath + "\\车辆信息汇总表" + DateTime.Now.ToString("yyyMMddHHmm") + ".xls",
                SheetName = "车辆",
                dt = dt2
            };
            Create(param);
        }
        private DataTable antlistToDataTable(AntList<DomesticAttendeeForTable> domesticAttendees)
        {

            return dt;
        }
        private void button1_Click(object sender, EventArgs e)
        {
            string strSelectedPath = SelectedFolderPath();

            if (string.IsNullOrWhiteSpace(strSelectedPath))
            {
                return;
            }
            DataTable dt2 = dt.Clone();
            var query = dt.AsEnumerable().Where<DataRow>(a => list.Contains(a["身份证件号码"].ToString()));
            foreach (DataRow item in query)
            {
                dt2.Rows.Add(item.ItemArray);
            }

            //Export1(strSelectedPath, dt2);
            //InitZip(strSelectedPath, dt2);
            Export1(strSelectedPath, dt);
            InitZip(strSelectedPath, dt);
            AntdUI.Message.success(window, "文件导出成功", autoClose: 1);
        }

        private bool CheckIfExitRegularData()
        {
            foreach (ListViewItem item in this.lvwUsers.Items)
            {
                if (item.ForeColor != Color.Red)
                {
                    list.Add(item.SubItems[2].Text);
                }
            }
            if (list.Count > 0)
                return true;
            return false;
        }
        private void InitZip(string strSelectedPath, DataTable dt2)
        {
            this.progress.Value = 10;
            // create export folder
            var zipDir = strSelectedPath + "\\境内人员" + DateTime.Now.ToString("yyyMMdd");
            if (!Directory.Exists(zipDir)) Directory.CreateDirectory(zipDir);

            this.progress.Value = 12;
            //tupian
            foreach (ListViewItem row in lvwUsers.Items)
            {
                //if(row.ForeColor == Color.Red) { continue; }
                ////var orgFullName = USER_PIC_FOLDER_PATH + "\\" + row.SubItems[3].Text;
                //var orgFullName = "orgFullName";
                //var nameFieldValue = row.SubItems[1].Text.ToString();
                //var idNumberFieldValue = row.SubItems[2].Text.ToString();
                //string picName = nameFieldValue + idNumberFieldValue + Path.GetExtension(orgFullName);
                //var tarFullName = userPicDir + "\\" + picName;
                //this.lblLiveMessage.Text = "正在拷贝-" + orgFullName;
                //this.lblLiveMessage.Refresh();
                //File.Copy(orgFullName, tarFullName, true);
            }
            var dtApplication = new DataTable("cart");
            dtApplication.Columns.Add(new DataColumn("序号", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("批次号", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("车牌号码", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("驾驶员姓名", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("身份证件类型", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("身份证件号码", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("电话号码", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("使用单位", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("申请证件类型", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("备注一", Type.GetType("System.String")));
             

            for (int i = 0; i < dt2.Rows.Count; i++)
            {
              
                DataRow dr = dtApplication.NewRow();
                string identityCard = dt2.Rows[i]["身份证件号码"].ToString();
                //if (identityCard.Length == 18)//处理18位的身份证号码从号码中得到生日和性别代码
                //{
                //    dr["出生日期"] = identityCard.Substring(6, 4) + "-" + identityCard.Substring(10, 2) + "-" + identityCard.Substring(12, 2);
                    
                //}
                //if (identityCard.Length == 15)
                //{
                //    dr["出生日期"] = "19" + identityCard.Substring(6, 2) + "-" + identityCard.Substring(8, 2) + "-" + identityCard.Substring(10, 2);
                  
                //}
           
                dr["序号"] = (i + 1);
                dr["车牌号码"] = dt2.Rows[i]["车牌号码"];
                dr["驾驶员姓名"] = dt2.Rows[i]["驾驶员姓名"];
                dr["身份证件类型"] = "C_ID";
                dr["身份证件号码"] = dt2.Rows[i]["身份证件号码"];
                dr["电话号码"] = dt2.Rows[i]["电话号码"];
                dr["使用单位"] = dt2.Rows[i]["使用单位"];
                dr["申请证件类型"] = dt2.Rows[i]["申请证件类型"];

                //dr[""] = dt2.Rows[i]["工作单位"];
                dr["备注一"] = "";
                dtApplication.Rows.Add(dr);
            }
            // create excel data file 
            this.progress.Value = 20;
            var xlsName = "车辆" + DateTime.Now.ToString("yyyMMdd") + ".xls";
            this.lblLiveMessage.Text = "生成-" + xlsName;
            this.lblLiveMessage.Refresh();
            var stream = DataTableToExcelXls(dtApplication, "车辆");
            byte[] bytes = new byte[stream.Length];
            stream.Read(bytes, 0, bytes.Length);
            stream.Seek(0, SeekOrigin.Begin);

            FileStream fs = new FileStream(zipDir + "\\" + xlsName, FileMode.Create);
            BinaryWriter bw = new BinaryWriter(fs);
            bw.Write(bytes);
            bw.Close();
            fs.Close();
            var zipPath = strSelectedPath + "\\" + "车辆" + DateTime.Now.ToString("yyyMMddHHmm") + ".fhp";

            ZipUtility.Create(zipDir, zipPath, ConfigurationManager.AppSettings["sheetPasse"].ToString());
            this.lblLiveMessage.Text = "已保存至[" + zipPath + "]";
            this.progress.Value = 70;
            DeleteFolder(zipDir);
            this.progress.Value = 100;
        }
        //private DataTable convertToExportDt(DataTable dt2)
        //{

        //}
        public static void Create(ApplicationFormParam param)
        {
            using (FileStream fs = new FileStream(param.FileFullPath, FileMode.Create, FileAccess.Write))
            {

                var workbook = new HSSFWorkbook();
                var sheet = workbook.CreateSheet(param.Title);

                var passe = ConfigurationManager.AppSettings["sheetPasse"].ToString();
                if (!string.IsNullOrWhiteSpace(passe)) sheet.ProtectSheet(passe);

                // title
                var titleStyle = workbook.CreateCellStyle();
                // 设置居中
                titleStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                var fontStyle = workbook.CreateFont();
                fontStyle.IsBold = true;
                fontStyle.FontName = "新宋体";
                fontStyle.FontHeightInPoints = 15;
                titleStyle.SetFont(fontStyle);
                titleStyle.WrapText = true;

                var titleRow = sheet.CreateRow(0);
                titleRow.Height = 40 * 20;
                var cellTitle = titleRow.CreateCell(0);
                cellTitle.SetCellValue(param.Title);
                cellTitle.CellStyle = titleStyle;
                //二维码
                string twoPic = Guid.NewGuid().ToString();
                byte[] bytes = QRcode(twoPic, "10", "10");
                HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                int pictureIdx = workbook.AddPicture(bytes, PictureType.PNG);
                /*插图片的位置HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 
                 * 前面四个表示在单元格中两个点的位置，后面四个表示是哪个单元格
                 * 图片左上角为(col1, row1)第row+1行col+1列，
                 * 右下角为(col2, row2)第col2+1行row2+1列，
                 * dx2控制图片高度，dy2控制图片宽度。
                 * dx2最大值1023，dy2最大值255
                 */
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 767, 255, 5, 0, 5, 1);
                //把图片插到相应的位置
                HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);

                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));

                // 设置字体
                var font = workbook.CreateFont();
                font.FontName = "新宋体";
                font.FontHeightInPoints = 11;


                var applicationDateRow = sheet.CreateRow(1);
                applicationDateRow.Height = 20 * 20;
                applicationDateRow.CreateCell(0).SetCellValue("组别：" + twoPic);

                var borderStyle = workbook.CreateCellStyle();
                borderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.TopBorderColor = HSSFColor.Black.Index;
                borderStyle.RightBorderColor = HSSFColor.Black.Index;
                borderStyle.BottomBorderColor = HSSFColor.Black.Index;
                borderStyle.LeftBorderColor = HSSFColor.Black.Index;
                borderStyle.SetFont(font);
                borderStyle.WrapText = true;

                var headerDateRow = sheet.CreateRow(2);
                for (int i = 0; i < param.dt.Columns.Count; i++)
                {
                    var strColumnName = param.dt.Columns[i].ColumnName;
                    var cell = headerDateRow.CreateCell(i);
                    cell.CellStyle = borderStyle;
                    cell.SetCellValue(strColumnName);
                    int length = 0;
                    if (strColumnName != "序号")
                    {
                        length = Encoding.Default.GetBytes(strColumnName).Length + 10;
                    }
                    else
                    {
                        length = 6;
                    }
                    sheet.SetColumnWidth(i, length * 256);
                }
                var rowIndex = 3;
                for (int i = 0; i < param.dt.Rows.Count; i++)
                {
                    var row = sheet.CreateRow(rowIndex);
                   
                    for (int j = 0; j < param.dt.Columns.Count; j++)
                    {
                        var strColumnName = param.dt.Columns[j].ColumnName;
                        var rowValue = param.dt.Rows[i][param.dt.Columns[j].ColumnName].ToString();
                        var cell = row.CreateCell(j);
                        cell.CellStyle = borderStyle;
                        if (strColumnName == "序号")
                        {
                            cell.SetCellValue((i + 1).ToString());
                        }
                        else
                        {
                            cell.SetCellValue(rowValue);
                        }
                        //var columnWidth = sheet.GetColumnWidth(i);
                        //int length = Encoding.Default.GetBytes(rowValue).Length + 2;
                        //var newColumnWidth = length * 256;
                        //if (newColumnWidth > columnWidth) sheet.SetColumnWidth(i, newColumnWidth);
                    }
                    rowIndex++;
                }

                var shenStyle = workbook.CreateCellStyle();
                shenStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                shenStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                shenStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                shenStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                shenStyle.TopBorderColor = HSSFColor.Black.Index;
                shenStyle.RightBorderColor = HSSFColor.Black.Index;
                shenStyle.BottomBorderColor = HSSFColor.Black.Index;
                shenStyle.LeftBorderColor = HSSFColor.Black.Index;
                shenStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                shenStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Justify;

                for (int i = rowIndex; i < rowIndex + 6; i++)
                {
                    var row = sheet.CreateRow(i);
                    for (int j = 0; j < 6; j++)
                    {
                        var cell = row.CreateCell(j);
                        if (j == 0)
                        {
                            cell.SetCellValue((" 审\n 批\n 人\n 意\n 见"));
                        }
                        cell.CellStyle = shenStyle;
                    }
                }


                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 5, 0, 0));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 5, 1, 5));



                var cardAdminRow = sheet.CreateRow(rowIndex + 7);
                cardAdminRow.CreateCell(0).SetCellValue("办理人：");
                cardAdminRow.CreateCell(2).SetCellValue("电  话：");
                cardAdminRow.CreateCell(4).SetCellValue("办理时间：");
                var unitPrincipalRow = sheet.CreateRow(rowIndex + 9);
                unitPrincipalRow.CreateCell(0).SetCellValue("领办人：");
                unitPrincipalRow.CreateCell(2).SetCellValue("电  话：");
                unitPrincipalRow.CreateCell(4).SetCellValue("领取时间：");
                workbook.Write(fs);
                fs.Flush(true);
            }

        }



        private static byte[] QRcode(string text, string width, string height)
        {

            try
            {
                BarcodeWriter writer = new BarcodeWriter();
                writer.Format = BarcodeFormat.QR_CODE;
                QrCodeEncodingOptions options = new QrCodeEncodingOptions();
                options.DisableECI = true;
                //设置内容编码
                options.CharacterSet = "UTF-8";
                //将传来的值赋给二维码的宽度和高度
                options.Width = Convert.ToInt32(width);
                options.Height = Convert.ToInt32(height);
                //设置二维码的边距,单位不是固定像素
                options.Margin = 1;
                writer.Options = options;

                Bitmap map = writer.Write(text);
                return BitmapToByte(map);

                //二维码会显示在桌面(你也想显示在桌面的话,要改一下路径)

            }
            catch (Exception)
            {
                return null;
            }

        }
        private static byte[] BitmapToByte(System.Drawing.Bitmap bitmap)
        {
            // 1.先将BitMap转成内存流
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            bitmap.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);
            ms.Seek(0, System.IO.SeekOrigin.Begin);
            // 2.再将内存流转成byte[]并返回
            byte[] bytes = new byte[ms.Length];
            ms.Read(bytes, 0, bytes.Length);
            ms.Dispose();
            return bytes;
        }
        public static Stream DataTableToExcelXls(DataTable dataTable, string sheetName)
        {
            try
            {
                const int startIndex = 0;
                var fields = dataTable.Columns;
                //创建excel数据结构
                var workbook = new HSSFWorkbook();
                //创建excel工作表
                var sheet = workbook.CreateSheet(sheetName);
                var borderStyle = workbook.CreateCellStyle();
                borderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.TopBorderColor = HSSFColor.Black.Index;
                borderStyle.RightBorderColor = HSSFColor.Black.Index;
                borderStyle.BottomBorderColor = HSSFColor.Black.Index;
                borderStyle.LeftBorderColor = HSSFColor.Black.Index;
            
                borderStyle.WrapText = true;
                var row = sheet.CreateRow(startIndex);
                for(int i = 0;i<dataTable.Columns.Count;i++)
                { 
                     DataColumn column = dataTable.Columns[i];
                    string strColumnName = column.ColumnName;
                    var cellIndex = fields.IndexOf(column) + startIndex;
                    var cell = row.CreateCell(cellIndex);
                    cell.CellStyle = borderStyle;
                    cell.SetCellValue(column.ColumnName);
                    sheet.AutoSizeColumn(cellIndex);
                    int length = 0;
                    if (strColumnName != "序号")
                    {
                        length = Encoding.Default.GetBytes(strColumnName).Length + 10;
                    }
                    else
                    {
                        length = 6;
                    }
                    sheet.SetColumnWidth(i, length * 256);
                }

                int rowIndex = 0;
                foreach (DataRow dataRow in dataTable.Rows)
                {
                    row = sheet.CreateRow(rowIndex + 1);
                    foreach (DataColumn column in dataTable.Columns)
                    {
                        var cellIndex = fields.IndexOf(column) + startIndex;
                        var cell = row.CreateCell(cellIndex);
                        var value = dataRow[column.ColumnName];
                        switch ((value ?? string.Empty).GetType().Name.ToLower())
                        {
                            case "int32":
                            case "int64":
                            case "decimal":
                                cell.SetCellValue(Convert.ToDouble(value));
                                break;
                            default:
                                cell.SetCellValue(Convert.ToString(value));
                                break;
                        }
                    }
                    rowIndex++;
                }

                MemoryStream ms = new MemoryStream();
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;//指定当前流的位置从0开始

                workbook = null;
                sheet = null;
                row = null;

                return ms;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
 
    }
}
